package org.sample.commons.edoc;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.charset.StandardCharsets;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {

    public static <T> void write(Columns columns, Collection<T> dataSet, OutputStream os) {
        try (Workbook workbook = new SXSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("sheet1");

            CellStyle headerStyle = getHeaderStyle(workbook, columns);
            CellStyle stringStyle = getStyle(workbook, columns);
            CellStyle numericStyle = getNumberStyle(workbook, columns);

            //获取第一行数据用于估算列宽
            T first = dataSet.iterator().next();
            for (int i = 0; i < columns.columns.size(); i++) {
                Column column = columns.columns.get(i);
                String value = column.readValue(first);
                int length = value.getBytes(StandardCharsets.UTF_8).length;
                sheet.setColumnWidth(i, length * 400);
            }

            Row row = sheet.createRow(0);
            for (int i = 0; i < columns.columns.size(); i++) {
                Column column = columns.columns.get(i);
                Cell cell = row.createCell(i);
                cell.setCellStyle(headerStyle);
                cell.setCellValue(column.title);
            }

            int rowNo = 1;
            for (T data : dataSet) {
                row = sheet.createRow(rowNo++);

                int cellNo = 0;
                for (Column column : columns.columns) {
                    String value = column.readValue(data);
                    Cell cell = row.createCell(cellNo++);

                    if (column.numeric) {
                        cell.setCellStyle(numericStyle);
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellValue(Double.parseDouble(value));
                    } else {
                        cell.setCellStyle(stringStyle);
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(value);
                    }
                }
            }
            workbook.write(os);
            os.flush();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获得标题样式
     */
    private static CellStyle getHeaderStyle(Workbook workbook, Columns columns) {
        CellStyle style = workbook.createCellStyle();
        if (columns.border) {
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
        }
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());

        Font font = workbook.createFont();
        font.setFontName(columns.fontName);
        font.setFontHeightInPoints((short) 11);
        font.setBold(false);
        font.setColor(IndexedColors.BLACK.getIndex());
        style.setFont(font);
        return style;
    }

    /**
     * 获得数值样式
     */
    private static CellStyle getNumberStyle(Workbook workbook, Columns columns) {
        CellStyle style = getStyle(workbook, columns);
        style.setAlignment(HorizontalAlignment.RIGHT);
        style.setDataFormat(FillPatternType.SPARSE_DOTS.getCode());
        return style;
    }

    /**
     * 获得文本样式
     */
    private static CellStyle getStyle(Workbook workbook, Columns columns) {
        CellStyle style = workbook.createCellStyle();
        if (columns.border) {
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
        }
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        Font font = workbook.createFont();
        font.setFontName(columns.fontName);
        font.setFontHeightInPoints((short) 11);
        font.setBold(false);
        style.setFont(font);
        return style;
    }

    /**
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     *
     * @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
     * @return 读出的Excel中数据的内容
     */
    public static List<String[]> read(InputStream in, int ignoreRows) {
        java.text.Format dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        java.text.Format decimalFormat = new DecimalFormat("0");

        List<String[]> result;
        try (Workbook workbook = new XSSFWorkbook(in)) {
            int totalRows = getTotalRows(workbook, ignoreRows);
            if (totalRows > 5000)
                throw new IOException("数据量大于5000条,可能导致内存溢出.");
            result = new ArrayList<>(totalRows);

            for (Sheet sheet : workbook) {

                for (Row row : sheet) {
                    if ((ignoreRows) > 0) {
                        ignoreRows--;
                        continue;
                    }

                    short cellSize = row.getLastCellNum();
                    String[] values = new String[cellSize];
                    boolean hasValue = false;
                    for (Cell cell : row) {
                        String value = null;
                        switch (cell.getCellTypeEnum()) {
                            case STRING:
                                value = cell.getStringCellValue();
                                break;
                            case NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    if (date != null)
                                        value = dateFormat.format(date);
                                } else {
                                    value = decimalFormat.format(cell.getNumericCellValue());
                                }
                                break;
                            case BOOLEAN:
                                value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                                break;
                            case FORMULA:
                                value = getFormulaValue(cell);
                                break;
                            default:
                                break;
                        }
                        values[cell.getColumnIndex()] = StringUtils.trimToNull(value);
                        if (value != null)
                            hasValue = true;
                    }
                    if (hasValue) {
                        result.add(values);
                    }
                }
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return result;
    }

    private static String getFormulaValue(Cell cell) {
        try {
            return String.valueOf(cell.getNumericCellValue());
        } catch (Exception e) {
            e.printStackTrace();
            return cell.getStringCellValue();
        }
    }

    private static int getTotalRows(Workbook workbook, int ignoreRows) {
        int totalRows = 0;
        for (Sheet sheet : workbook) {
            totalRows += sheet.getLastRowNum() + 1 - ignoreRows;
        }
        return totalRows;
    }
}